package Dao.Grade.Imp;

import Dao.Grade.GroupGradeDao;
import Entity.Grade.GroupGrade;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GroupGradeDaoImp implements GroupGradeDao {
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    @Override
    public List<GroupGrade> getAllGroupGradeByNO(String queryKey, Integer offset, Integer limit, String NO, Integer type, Connection conn) {
        List<GroupGrade> groupGrades = new ArrayList<>();
        try {
            String sql;
            if (type == 1) {
                sql = "select " +
                        "id, sno, tno, remark, grade1, grade2, grade3, grade4, reply, grade_state, pass_state " +
                        "from group_grade " +
                        "where sno = ? limit ?,?";
            } else {
                sql = "select " +
                        "id, sno, tno, remark, grade1, grade2, grade3, grade4, reply, grade_state, pass_state " +
                        "from group_grade " +
                        "where tno = ? limit ?,? ";
            }
            ps = conn.prepareStatement(sql);
            ps.setString(1, NO);
            ps.setInt(2, offset);
            ps.setInt(3, limit);
            rs = ps.executeQuery();
            while (rs.next()) {
                GroupGrade grade = setGroupGrade(rs);

                groupGrades.add(grade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return groupGrades;
    }

    @Override
    public Integer addGroupGrade(GroupGrade grade, Connection conn) {
        int result = 0;
        try {
            String sql = " insert into group_grade(sno, tno, remark, grade1, grade2, grade3, grade4, reply, grade_state, pass_state) " +
                    " values (?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, grade.getSno());
            ps.setString(2, grade.getTno());
            ps.setString(3, grade.getRemark());
            ps.setInt(4, grade.getGrade1());
            ps.setInt(5, grade.getGrade2());
            ps.setInt(6, grade.getGrade3());
            ps.setInt(7, grade.getGrade4());
            ps.setString(8, grade.getReply());
            ps.setString(9, grade.getGradeState());
            ps.setString(10, grade.getPassState());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    @Override
    public GroupGrade getGroupGradeByNOAndReply(String SNO, String reply, Connection conn) {
        GroupGrade groupGrade = null;
        try {
            String sql = "select " +
                    "id, sno, tno, remark, grade1, grade2, grade3, grade4, reply, grade_state, pass_state " +
                    "from group_grade " +
                    "where sno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, SNO);
            rs = ps.executeQuery();
            if (rs.next()) {
                groupGrade = setGroupGrade(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return groupGrade;
    }

    @Override
    public Integer updateGroupGradeByNOAndReply(GroupGrade grade, Connection conn) {
        int result = 0;
        try {
            String sql = " update group_grade set  remark = ?, grade1 = ?, grade2 = ?, grade3 = ?, grade4 = ?, grade_state = ?, pass_state = ?,reply = '一辩' " +
                    " where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, grade.getRemark());
            ps.setInt(2, grade.getGrade1());
            ps.setInt(3, grade.getGrade2());
            ps.setInt(4, grade.getGrade3());
            ps.setInt(5, grade.getGrade4());
            ps.setString(6, grade.getGradeState());
            ps.setString(7, grade.getPassState());
            ps.setString(8, grade.getSno());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    @Override
    public Integer insertSNO(String SNO, Connection conn) {
        int result = 0;
        try {
            String sql = "  insert into group_grade(sno) values (?) ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, SNO);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    @Override
    public void delGrading(Connection conn) {
        try {
            String sql = "  delete from group_grade ";
            ps = conn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
    }

    @Override
    public Integer insertSNOAndTNO(String SNO, String TNO, Connection conn) {
        int result = 0;
        try {
            String sql = " insert into group_grade(sno, tno) " +
                    " values (?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, SNO);
            ps.setString(2, TNO);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }


    public GroupGrade setGroupGrade(ResultSet rs) {
        GroupGrade grade = null;
        try {
            Integer id = rs.getInt(1);
            String sno = rs.getString(2);
            String tno = rs.getString(3);
            String remark = rs.getString(4);
            Integer grade1 = rs.getInt(5);
            Integer grade2 = rs.getInt(6);
            Integer grade3 = rs.getInt(7);
            Integer grade4 = rs.getInt(8);
            String reply = rs.getString(9);
            String gradeState = rs.getString(10);
            String passState = rs.getString(11);
            grade = new
                    GroupGrade(id, sno, tno, remark, grade1, grade2, grade3, grade4, reply, gradeState, passState, null);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return grade;
    }


}
